package merge;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class POIUtils {
    public class XSSFDateUtil extends DateUtil {  

    }  
    public static void copyCellStyle(XSSFCellStyle fromStyle, XSSFCellStyle toStyle) {

        toStyle.cloneStyleFrom(fromStyle);//此一行代码搞定
        
    }  
    public static void mergeSheetAllRegion(XSSFSheet fromSheet, XSSFSheet toSheet) {//合并单元格
        int num = fromSheet.getNumMergedRegions();
        CellRangeAddress cellR = null;
        for (int i = 0; i < num; i++) {
            cellR = fromSheet.getMergedRegion(i);
            toSheet.addMergedRegion(cellR);
        }
    } 

    public static void copyCell(XSSFWorkbook wb,XSSFCell fromCell, XSSFCell toCell) {  
        XSSFCellStyle newstyle=wb.createCellStyle();  
        copyCellStyle(fromCell.getCellStyle(), newstyle);  
        //toCell.setEncoding(fromCell.getEncoding());  
        //样式  
        toCell.setCellStyle(newstyle);   
        if (fromCell.getCellComment() != null) {  
            toCell.setCellComment(fromCell.getCellComment());  
        }  
        // 不同数据类型处理  
        int fromCellType = fromCell.getCellType();  
        toCell.setCellType(fromCellType);  
        if (fromCellType == XSSFCell.CELL_TYPE_NUMERIC) {  
            if (XSSFDateUtil.isCellDateFormatted(fromCell)) {  
                    toCell.setCellValue(fromCell.getDateCellValue());  
                } else {  
                    toCell.setCellValue(fromCell.getNumericCellValue());  
                }  
            } else if (fromCellType == XSSFCell.CELL_TYPE_STRING) {  
                toCell.setCellValue(fromCell.getRichStringCellValue());  
            } else if (fromCellType == XSSFCell.CELL_TYPE_BLANK) {  
                // nothing21  
            } else if (fromCellType == XSSFCell.CELL_TYPE_BOOLEAN) {  
                toCell.setCellValue(fromCell.getBooleanCellValue());  
            } else if (fromCellType == XSSFCell.CELL_TYPE_ERROR) {  
                toCell.setCellErrorValue(fromCell.getErrorCellValue());  
            } else if (fromCellType == XSSFCell.CELL_TYPE_FORMULA) {  
                toCell.setCellFormula(fromCell.getCellFormula());  
            } else { // nothing29  
            }  

    }  

    public static void copyRow(XSSFWorkbook wb,XSSFRow oldRow,XSSFRow toRow){  
        toRow.setHeight(oldRow.getHeight());
        for (Iterator cellIt = oldRow.cellIterator(); cellIt.hasNext();) {  
            XSSFCell tmpCell = (XSSFCell) cellIt.next();  
            XSSFCell newCell = toRow.createCell(tmpCell.getColumnIndex());  
            copyCell(wb,tmpCell, newCell);  
        }  
    }  
    public static void copySheet(XSSFWorkbook wb,XSSFSheet fromSheet, XSSFSheet toSheet) {   
        mergeSheetAllRegion(fromSheet, toSheet);     
        //设置列宽
        for(int i=0;i<=fromSheet.getRow(fromSheet.getFirstRowNum()).getLastCellNum();i++){ 
            toSheet.setColumnWidth(i,fromSheet.getColumnWidth(i)); 
        } 
        for (Iterator rowIt = fromSheet.rowIterator(); rowIt.hasNext();) {  
            XSSFRow oldRow = (XSSFRow) rowIt.next(); 
            XSSFRow newRow = toSheet.createRow(oldRow.getRowNum()); 
            copyRow(wb,oldRow,newRow);  
        }  
    }  

    public static void mergeExcelFiles(String targetPath,String targetFileName, List<String> excelfiles) throws IOException{
    	//将所有类型的尽调excel文件合并成一个excel文件
    	XSSFWorkbook newExcelCreat = new XSSFWorkbook(); 
    	for(String fromExcelName:excelfiles) {//遍历每个源excel文件，fileNameList为源文件的名称集合
    	     InputStream in = new FileInputStream(targetPath+ File.separator +fromExcelName);
    	     XSSFWorkbook fromExcel = new XSSFWorkbook(in);
    	     for(int i = 0; i < fromExcel.getNumberOfSheets(); i++) {//遍历每个sheet
    	         XSSFSheet oldSheet = fromExcel.getSheetAt(i);
    	         XSSFSheet newSheet = newExcelCreat.createSheet(oldSheet.getSheetName()); 
    	         POIUtils.copySheet(newExcelCreat, oldSheet, newSheet);
    	     } 
    	 }
    	 String allFileName=targetPath+File.separator+targetFileName+".xlsx";
    	 FileOutputStream fileOut = new FileOutputStream(allFileName);
    	 newExcelCreat.write(fileOut); 
    	 fileOut.flush(); 
    	 fileOut.close();
    	 //删除各个源文件
    	 for(String fromExcelName:excelfiles) {//遍历每个源excel文件
    	     File file=new File(targetPath+ File.separator +fromExcelName);
    	     if(file.exists()){
    	         file.delete();
    	     } 
    	 }
    }
    
    public static void main(String[] args) {
		List<String> files=new ArrayList<>();
		files.add("map_students1.xlsx");
		files.add("map_students2.xlsx");
		files.add("map_students3.xlsx");
		
		String targpath="F:\\dd";
		try {
			mergeExcelFiles(targpath,"map_students",files);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
